﻿using Newtonsoft.Json;
using SuperSocket.SocketBase.Command;
using SuperSocket.SocketBase.Protocol;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading;
using System.Windows.Forms;

namespace ETS_FinancialData_sync_toolsForServer
{
    public class Json : CommandBase<TelnetSession, StringRequestInfo>
    {
        public int count = 0;
        public static DataTable dt = null;
        public static bool DebugModel = false;
        public override void ExecuteCommand(TelnetSession session, StringRequestInfo requestInfo)
        {
            Console.WriteLine("Received Key:" + requestInfo.Key);
            Console.WriteLine("Received Body :" + requestInfo.Body);
            try
            {
                //将json还原为一个同步消息列表
                List<Dictionary<string, Object>> list = JsonConvert.DeserializeObject<List<Dictionary<string, Object>>>(requestInfo.Body);

                //循环读取同步消息
                List<Dictionary<string, Object>> returnList = new List<Dictionary<string, Object>>();
                foreach (Dictionary<string, Object> obj in list)
                {
                    //字典过滤
                    //FilterString(obj);
                      if (DebugModel)
                          DbHelper.saveErrLog(":过滤处理前" + requestInfo.Body + "\n\r", DateTime.Now.ToString("yyyy-MM-dd") + "-log");

                    Dictionary<string, Object> returnDict = null;

                    if (obj["data_type"].ToString() == "sys_workshop")
                    {
                        //过滤此表的字段
                        FilterString(obj, null, "SHOP_CODE", null);

                        returnDict = DbHelper.handle_table(obj, "SHOP_CODE");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "sys_user")
                    {
                        //过滤此表的字段
                        FilterString(obj, "empid", "USER_ID,WORKSHOP_ID", null);

                        returnDict = DbHelper.handle_table(obj, "USER_ID");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "sys_payment")
                    {
                        //过滤此表的字段
                        FilterString(obj, "detail_type", null, "code");

                        returnDict = DbHelper.handle_table(obj, "code");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "syskindacc")
                    {
                        //过滤此表的字段
                        FilterString(obj, null, null, "code");

                        returnDict = DbHelper.handle_table(obj, "code");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "fitness_checkin")
                    {
                        //过滤此表的字段
                        FilterString(obj, "operator,mem_type,memid", "sid,cardno,memiden,workshop,fbillNo", null);

                        returnDict = DbHelper.handle_table(obj, "sid");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "pos_bill_payment")
                    {
                        //过滤此表的字段
                        FilterString(obj, "paymentid,memid,userid", "billcode,payshop", "paymethod,payparent,paycode");

                        returnDict = DbHelper.handle_table(obj, "paymentid");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "pos_sales")
                    {
                        //过滤此表的字段
                        FilterString(obj, "saleid,userid", "billcode,billshop,grand_billcode,saleshop,payshop,memno", "closemethod,paymethod,sale_kinds,parent_kinds,sale_category,parent_category,acc_clubkinds3");

                        returnDict = DbHelper.handle_table(obj, "saleid");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "pos_bills")
                    {
                        //过滤此表的字段
                        FilterString(obj, "openuserid,closeuserid,memsid,accuserid,memid", "billcode,billshop,grand_billcode,saleshop,payshop,memno,guest_identity", "paymethod,paycode");

                        returnDict = DbHelper.handle_table(obj, "billcode");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "fitness_cardchange")
                    {
                        //过滤此表的字段
                        FilterString(obj, "sid,memkind,newmemkind,usercode,memid,sale_name", "cardno,newcardno", null);

                        returnDict = DbHelper.handle_table(obj, "sid");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "jxc_bm_ck")
                    {
                        returnDict = DbHelper.handle_table(obj, "bm");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "memcardlog")
                    {
                        //过滤此表的字段
                        FilterString(obj, "sid,memid,usercode,timeitem,sale_name", "cardno,workshop", null);

                        returnDict = DbHelper.handle_table(obj, "sid");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "mem_member")
                    {
                        //过滤此表的字段
                        FilterString(obj, "q_id,user_id,memberkind,sale_name,modify" , "m_id,new_m_id,tickh,mem_iden,main_id,houseid,billtocard", null);

                        returnDict = DbHelper.handle_table(obj, "m_id");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "fitness_enroll_qchange")
                    {
                        //过滤此表的字段
                        FilterString(obj, "sid,operator", "class,cardno,foldclass", null);

                        returnDict = DbHelper.handle_table(obj, "sid");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "sys_identity")
                    {
                        //过滤此表的字段
                        FilterString(obj, null, "code", null);

                        returnDict = DbHelper.handle_table(obj, "code");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "fitness_classes")
                    {
                        //过滤此表的字段
                        FilterString(obj, "instructor", "code", null);

                        returnDict = DbHelper.handle_table(obj, "code");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "mem_kind")
                    {
                        //过滤此表的字段
                        FilterString(obj, "memberkind", "icno", null);

                        returnDict = DbHelper.handle_table(obj, "memberkind");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "SnArt")
                    {
                        //过滤此表的字段
                        FilterString(obj, "v_artNo", null, null);

                        returnDict = DbHelper.handle_table(obj, "v_artNo");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "gym_reserve")
                    {
                        //过滤此表的字段
                        FilterString(obj, "sid,time_mode,period_id,res_item,operator,memid", "cardno,dcbillcode", null);

                        returnDict = DbHelper.handle_table(obj, "sid");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "fitness_enroll")
                    {
                        //过滤此表的字段
                        FilterString(obj, "sid,operator,memid,fartjlno,sale_name", "class,cardno", null);

                        returnDict = DbHelper.handle_table(obj, "sid");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "gym_period")
                    {
                        //过滤此表的字段
                        FilterString(obj, "sid,time_mode", null, null);

                        returnDict = DbHelper.handle_table(obj, "sid");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "t_fsl_sk_student05")
                    {
                        //过滤此表的字段
                        FilterString(obj, "fpartno,fbbjlman", "v_bcname,fbillNo,kh", null);

                        returnDict = DbHelper.handle_table(obj, "fbillNo");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "gym_item")
                    {
                        //过滤此表的字段
                        FilterString(obj, "item_code,time_mode", "fbillNo", null);

                        returnDict = DbHelper.handle_table(obj, "item_code");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "fitness_monthfee")
                    {
                        //过滤此表的字段
                        FilterString(obj, "sid,usercode,memid,sale_name", "cardno,billcode,memo", null);

                        returnDict = DbHelper.handle_table(obj, "sid");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "gym_kind")
                    {
                        //过滤此表的字段
                        FilterString(obj, "code", "shop_code", null);

                        returnDict = DbHelper.handle_table(obj, "code");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "jxc_dj_kc_2")
                    {
                        returnDict = DbHelper.handle_table(obj, "dh,xh");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "jxc_dj_kc_zc_2")
                    {
                        returnDict = DbHelper.handle_table(obj, "dh,xh");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "jxc_dj_kc_db_2")
                    {
                        returnDict = DbHelper.handle_table(obj, "dh,xh");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "jxc_dj_xs_2")
                    {
                        returnDict = DbHelper.handle_table(obj, "dh,xh");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "jxc_dj_gh_2")
                    {
                        returnDict = DbHelper.handle_table(obj, "dh,xh");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "jxc_bm_sp")
                    {
                        returnDict = DbHelper.handle_table(obj, "bm");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "jxc_zb_sp_kc")
                    {
                        returnDict = DbHelper.handle_table(obj, "bm_ck,bm_sp,jxc_yyyymm");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "gym_status")
                    {
                        //过滤此表的字段
                        FilterString(obj, "period_id,item_code,time_mode", "sid,operator", null);

                        returnDict = DbHelper.handle_table(obj, "sid");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                    if (obj["data_type"].ToString() == "card_times_info")
                    {
                        //过滤此表的字段
                        FilterString(obj, "timescode", null, null);

                        returnDict = DbHelper.handle_table(obj, "timescode");
                        returnDict["ver"] = DbHelper.Ver;
                        returnList.Add(returnDict);
                    }
                }
                String returnMsg = JsonConvert.SerializeObject(returnList);
                session.Send(string.Format("return##{0}", returnMsg + "||"));

            }
            catch (Exception ex)
            {

                DbHelper.saveErrLog(ex.Message + "\n\r" + ex.StackTrace + "\n\r" + requestInfo.Body, "ex");

            }
        }
        //财务参数指定的字段值不加分公司
        //string[] kindaccCodeArr = { "$54", "$56", "$76", "$77", "$80", "$82", "$85", "$86", "$88", "$90", "$99", "$91", "$93", "$95", "$97", "54", "56", "76", "77", "80", "82", "85", "86", "88", "90", "91", "93", "95", "97", "99" };

        //付款方式指定的字段值不加分公司
        //string[] paymentCodeArr = { "0001", "0002", "0003", "0004", "0006", "0007", "0008", "1001", "1003", "2001", "2002", "2003", "3001", "6999", "7999" };

        public static ArrayList kindaccCodeArr = null;
        public static ArrayList paymentCodeArr = null;
        //给指定的列的值加后缀避免重复
        private void FilterString(Dictionary<string, Object> obj, string fclubField, string letterField, string judgmentField)
        {
            //替换分公司
            if (obj.ContainsKey("fclub"))
            {
                obj["fclub"] = DbHelper.plus_string(obj)["fclub"].ToString();
            }
            if (obj.ContainsKey("Fclub"))
            {
                obj["Fclub"] = DbHelper.plus_string(obj)["fclub"].ToString();
            }
            if (obj.ContainsKey("FClub"))
            {
                obj["FClub"] = DbHelper.plus_string(obj)["fclub"].ToString();
            }
            if (obj.ContainsKey("clubcode"))
            {
                obj["clubcode"] = DbHelper.plus_string(obj)["fclub"].ToString();
            }
            //需要加分公司编号的列
            if (fclubField != null)
            {
                string[] field = fclubField.Split(',');
                //遍历列数组
                foreach (string item in field)
                {
                    //判断字典是否存在&&是否包含不为空的列值
                    if (obj.ContainsKey(item) && obj[item].ToString().Length!=0)
                    {
                        if (obj[item].GetType() == typeof(string))
                        {
                            //如果是加上分公司编号后缀
                            obj[item] += DbHelper.plus_string(obj)["fclub"].ToString();
                        }
                        if (obj[item].GetType() == typeof(Int32) || obj[item].GetType() == typeof(Double) || obj[item].GetType() == typeof(Decimal))
                        {
                            if(Convert.ToDouble(obj[item])!=0)
                                obj[item] = Convert.ToInt64(obj[item] + DbHelper.plus_string(obj)["fclub"].ToString());
                        }
                    }
                }
            }

            //需要加指定字母的列
            if (letterField != null)
            {
                string[] field = letterField.Split(',');
                foreach (string item in field)
                {
                    //判断字典是否存在&&是否包含不为空的列值
                    if (obj.ContainsKey(item) && obj[item].ToString().Length != 0)
                    {
                        //将该列的值加上指定字母后缀
                        obj[item] =obj[item].ToString().Trim()+DbHelper.plus_string(obj)["string"].ToString();
                    }
                }
            }
            //需要判断值列的值后，加分公司编号
            if (judgmentField != null)
            {
                string[] field = judgmentField.Split(',');
                foreach (string item in field)
                {
                    if (obj.ContainsKey(item))
                    {
                        //如果和付款方式的列有关
                        if (item == "paymethod" || item == "closemethod" || item == "payparent" || (item == "code" && obj["data_type"].ToString() == "sys_payment"))
                        {
                            //判断不在系统默认列值内的和不为空的加上分公司后缀
                            if (!paymentCodeArr.Contains(obj[item].ToString())&&obj[item].ToString().Length!=0 )
                            {
                                obj[item] += DbHelper.plus_string(obj)["fclub"].ToString();
                            }
                        }
                        else
                        {
                            if (!kindaccCodeArr.Contains(obj[item].ToString()) && obj[item].ToString().Length != 0)
                            {
                                obj[item] += DbHelper.plus_string(obj)["fclub"].ToString();
                            }
                        }
                    }
                }
            }
        }
        //给指定的列的值加后缀避免重复
        private void FilterString(Dictionary<string, Object> obj)
        {
            //替换分公司
            if (obj.ContainsKey("fclub"))
            {
                obj["fclub"] = DbHelper.plus_string(obj)["fclub"].ToString();
            }
            if (obj.ContainsKey("Fclub"))
            {
                obj["Fclub"] = DbHelper.plus_string(obj)["fclub"].ToString();
            }
            if (obj.ContainsKey("FClub"))
            {
                obj["FClub"] = DbHelper.plus_string(obj)["fclub"].ToString();
            }


            //字符串型的主键在后面加指定的字符串
            if (obj.ContainsKey("billcode"))
            {
                obj["billcode"] += DbHelper.plus_string(obj)["string"].ToString();
            }
            if (obj.ContainsKey("grand_billcode"))
            {
                obj["grand_billcode"] += DbHelper.plus_string(obj)["string"].ToString();
            }
            if (obj.ContainsKey("fit_paymentid"))
            {
                obj["fit_paymentid"] += DbHelper.plus_string(obj)["string"].ToString();
            }
            if (obj.ContainsKey("fit_saleid"))
            {
                obj["fit_saleid"] += DbHelper.plus_string(obj)["string"].ToString();
            }
            if (obj.ContainsKey("fbillno"))
            {
                obj["fbillno"] += DbHelper.plus_string(obj)["string"].ToString();
            }
            if (obj.ContainsKey("fbillnosub"))
            {
                obj["fbillnosub"] += DbHelper.plus_string(obj)["string"].ToString();
            }

            //数字型的主键在后面加分公司编号
            if (obj.ContainsKey("saleid"))
            {
                obj["saleid"] = Convert.ToInt64(obj["saleid"] + DbHelper.plus_string(obj)["fclub"].ToString());
            }
            if (obj.ContainsKey("paymethod"))
            {
                if (!paymentCodeArr.Contains(obj["paymethod"].ToString()))
                    obj["paymethod"] += DbHelper.plus_string(obj)["fclub"].ToString();
            }
            if (obj.ContainsKey("paycode"))
            {
                if (!kindaccCodeArr.Contains(obj["paycode"].ToString()))
                    obj["paycode"] += DbHelper.plus_string(obj)["fclub"].ToString();
            }
            if (obj.ContainsKey("sale_kinds"))
            {
                if (!kindaccCodeArr.Contains(obj["sale_kinds"].ToString()))
                    obj["sale_kinds"] += DbHelper.plus_string(obj)["fclub"].ToString();
            }
            if (obj.ContainsKey("parent_kinds"))
            {
                if (!kindaccCodeArr.Contains(obj["parent_kinds"].ToString()))
                    obj["parent_kinds"] += DbHelper.plus_string(obj)["fclub"].ToString();
            }
            if (obj.ContainsKey("sale_category"))
            {
                if (!kindaccCodeArr.Contains(obj["sale_category"].ToString()))
                    obj["sale_category"] += DbHelper.plus_string(obj)["fclub"].ToString();
            }
            if (obj.ContainsKey("parent_category"))
            {
                if (!kindaccCodeArr.Contains(obj["parent_category"].ToString()))
                    obj["parent_category"] += DbHelper.plus_string(obj)["fclub"].ToString();
            }
            if (obj.ContainsKey("payparent"))
            {
                if (!paymentCodeArr.Contains(obj["payparent"].ToString()))
                    obj["payparent"] += DbHelper.plus_string(obj)["fclub"].ToString();
            }
            if (obj.ContainsKey("sid"))
            {
                obj["sid"] += DbHelper.plus_string(obj)["fclub"].ToString();
            }
            //if (obj.ContainsKey("syscode"))
            //{
            //    obj["syscode"] += DbHelper.plus_string(obj)["fclub"].ToString();
            //}
            if (obj.ContainsKey("code"))
            {
                if (obj["data_type"].ToString() == "syskindacc")
                {
                    if (!kindaccCodeArr.Contains(obj["code"].ToString()))
                        obj["code"] += DbHelper.plus_string(obj)["fclub"].ToString();
                }
                else if (obj["data_type"].ToString() == "sys_payment")
                {
                    if (!paymentCodeArr.Contains(obj["code"].ToString()))
                        obj["code"] += DbHelper.plus_string(obj)["fclub"].ToString();
                }
            }
            if (obj.ContainsKey("parent_code"))
            {
                if (!paymentCodeArr.Contains(obj["sale_kinds"].ToString()))
                    obj["parent_code"] += DbHelper.plus_string(obj)["fclub"].ToString();
            }
            if (obj.ContainsKey("closemethod"))
            {
                if (!paymentCodeArr.Contains(obj["sale_kinds"].ToString()))
                    obj["closemethod"] += DbHelper.plus_string(obj)["fclub"].ToString();
            }
            if (obj.ContainsKey("paymentid"))
            {
                obj["paymentid"] = Convert.ToInt64(obj["paymentid"] + DbHelper.plus_string(obj)["fclub"].ToString());
            }

            //if (field == "saleid" || field == "paymethod" || field == "paycode" || field == "sale_kinds" || field == "parent_kinds" ||
            //    field == "sale_category" || field == "parent_category" || field == "payparent" || field == "sid" || field == "syscode" ||
            //    field == "code" || field == "parent_code" || field == "closemethod" || field == "paymentid") { }
        }

        public class DbHelper
        {

            #region 判断传入的值是否为空
            /// <summary>
            /// 判断传入的值是否为空
            /// </summary>
            /// <param name="o"></param>
            /// <returns></returns>
            public static string ifIsNull(Object o)
            {
                String rtn = null;
                if (o != null && o.ToString().Length > 0)
                {
                    rtn = "'" + Convert.ToDateTime(o).ToString() + "'";
                }
                else
                {
                    rtn = "null";
                }
                return rtn;
            }

            public static string toNull(object value)
            {

                string retunValue = null;
                if (value.ToString().Length == 0)
                {
                    retunValue = "null";
                }
                else
                {
                    retunValue = value.ToString();
                }
                return retunValue;
            }

            public static string toNull(Dictionary<string, Object> dict, string key)
            {
                string returnValue = dict[key].ToString();
                if (dict[key].ToString().Length == 0)
                {
                    return "null";
                }
                if (dict[key].GetType() == typeof(string))
                {
                    returnValue = string.Format("'{0}'", dict[key].ToString().Replace("'", ""));//有的值有'导致生成的sql语法不规范
                }
                else if (dict[key].GetType() == typeof(DateTime))
                {
                    returnValue = string.Format("'{0}'", dict[key]);
                }
                return returnValue;
            }
            //是否包含了默认的key
            //如果是返回true
            //遇到contine跳过当前循环
            public static bool ifHasDefaultKeys(string key)
            {
                return (key == "LocalId" || key == "sync_type" || key == "data_type" || key == "sync_id" || key == "timestamp" || key == "new_timestamp");
            }
            #endregion

            /// <summary>
            /// 根据字典生成sql插入语句
            /// </summary>
            /// <param name="dict"></param>
            /// <returns>sql插入语句</returns>
            public static string DictToInsertSQL(Dictionary<string, Object> dict)
            {
                StringBuilder sqlText = new StringBuilder();
                //sqlText.AppendLine("SET IDENTITY_INSERT  " + dict["data_type"] + " on ");
                sqlText.AppendFormat("Insert {0} (", dict["data_type"]);
                int i = 0;
                if (dict.ContainsKey("new_m_id"))
                {
                    dict.Remove("new_m_id");
                }
                foreach (string key in dict.Keys)
                {
                    if (ifHasDefaultKeys(key))
                        continue;
                    if (i == 0)
                    {
                        sqlText.AppendFormat("[{0}]", key.ToString());
                    }
                    else
                    {
                        sqlText.AppendFormat(", [{0}]", key.ToString());
                    }
                    i++;
                }
                sqlText.Append(") Values(");
                i = 0;
                foreach (string key in dict.Keys)
                {

                    if (ifHasDefaultKeys(key))
                        continue;
                    if (i == 0)
                    {

                        sqlText.AppendFormat("{0}", toNull(dict, key));
                    }
                    else
                    {
                        sqlText.AppendFormat(",{0}", toNull(dict, key));
                    }

                    i++;
                }
                sqlText.Append(");");
                //sqlText.AppendLine("SET IDENTITY_INSERT  " + dict["data_type"] + " off ");
                return sqlText.ToString();
            }

            /// <summary>
            /// 根据字典和主键生成更新sql
            /// </summary>
            /// <param name="dict">json转换的字典</param>
            /// <param name="primaryKey">主键</param>
            /// <returns>sql</returns>
            public static string DictToUpdateSQL(Dictionary<string, Object> dict, string primaryKey)
            {
                StringBuilder sqlText = new StringBuilder();
                int i = 0;
                //定义一个update头
                sqlText.AppendFormat("update {0} set ", dict["data_type"]);
                //遍历字典，生成sql赋值语句
                foreach (string key in dict.Keys)
                {
                    //会员补卡更改主键的bug修复
                    if (key == "new_m_id")
                    {
                        //如果不是第一个列
                        if (i != 0) sqlText.Append(",");
                        sqlText.Append("m_id=" + toNull(dict, key));
                        i++;
                        continue;
                    }
                    //如果key为主键或者key是默认的则跳过本次循环
                    if (key == primaryKey || (ifHasDefaultKeys(key)))
                    {
                        continue;
                    }
                    //如果不是第一个列
                    if (i != 0) sqlText.Append(",");
                    sqlText.Append(key + "=" + toNull(dict, key));
                    i++;
                }
                //加上where条件
                //单个主键和多个主键处理
                if (!PKCodesHandle(primaryKey, ref sqlText, dict))
                    sqlText.Append(" where " + primaryKey + "=" + toNull(dict, primaryKey) + ";");
                //如果字典没有更新的列，返回null
                if (i == 0)
                {
                    return null;
                }
                return sqlText.ToString();
            }

            /// <summary>
            /// 根据字典生成删除SQL
            /// </summary>
            /// <param name="dict">json转换的字典</param>
            /// <param name="primaryKey">主键</param>
            /// <returns>sql</returns>
            public static string DictToDeleteSQL(Dictionary<string, Object> dict, string primaryKey)
            {
                StringBuilder sqlText = new StringBuilder();
                sqlText.AppendFormat("delete from " + dict["data_type"]);
                //单个主键和多个主键处理
                if (!PKCodesHandle(primaryKey, ref sqlText, dict))
                    sqlText.Append(" where " + primaryKey + "=" + toNull(dict, primaryKey) + ";");
                return sqlText.ToString();
            }

            public static bool PKCodesHandle(string content, ref StringBuilder sqlText, Dictionary<string, Object> dict)
            {
                bool ifHasPKCodes = false;
                if (content.Contains(","))
                {
                    string[] ArrPK = content.Split(',');
                    sqlText.Append(" where ");
                    for (int i = 0; i < ArrPK.Length; i++)
                    {
                        //如果是第一个就不需要加and
                        if (i == 0)
                        {
                            sqlText.Append(ArrPK[0] + "=" + toNull(dict, ArrPK[0]));
                        }
                        else
                        {
                            sqlText.Append(" and " + ArrPK[i] + "=" + toNull(dict, ArrPK[i]));
                        }
                    }
                    return true;
                }
                else
                {
                    return ifHasPKCodes;
                }
            }

            #region //处理所有表的方法
            /// <summary>
            /// 处理所有表的方法
            /// </summary>
            /// <param name="dict"></param>
            /// <returns></returns>
            public static Dictionary<string, Object> handle_table(Dictionary<string, Object> dict, string primaryKey)
            {
                if (DebugModel)
                    DbHelper.saveErrLog(":过滤处理后" + JsonConvert.SerializeObject(dict) + "\n\r", DateTime.Now.ToString("yyyy-MM-dd") + "-log");

                Dictionary<string, Object> returnDict = new Dictionary<string, Object>();
                //TODO: 下面要补上数据库操作的代码
                string sql = "";
                try
                {

                    if (ifSync_idExist("select COUNT(*) from t_sync where sync_id='" + dict["sync_id"] + "'") > 0)
                    {
                        returnDict["sync_id"] = dict["sync_id"];
                        returnDict["status"] = 1;
                        returnDict["remark"] = "这个sync_id已经处理过啦。";
                        return returnDict;
                    }
                    if (DebugModel == true)
                    {
                        //测试模式，不执行sql脚本
                        sql = null; dict["sync_type"] = 4;
                    }
                    if (Convert.ToInt32(dict["sync_type"]) == 0)
                    {
                        sql = DictToInsertSQL(dict);

                    }
                    else if (Convert.ToInt32(dict["sync_type"]) == 1)
                    {
                        sql = DictToUpdateSQL(dict, primaryKey);

                    }
                    else if (Convert.ToInt32(dict["sync_type"]) == 2)
                    {
                        sql = DictToDeleteSQL(dict, primaryKey);

                    }
                    if (sql == null)
                    {
                        returnDict["sync_id"] = dict["sync_id"];
                        returnDict["status"] = 1;
                        exec_sql("insert into t_sync(sync_id,fdate) values('" + dict["sync_id"] + "','" + DateTime.Now + "')");
                        return returnDict;
                    }
                    if (exec_sql(sql) > 0)
                    {
                        returnDict["sync_id"] = dict["sync_id"];
                        returnDict["status"] = 1;
                        exec_sql("insert into t_sync(sync_id,fdate) values('" + dict["sync_id"] + "','" + DateTime.Now + "')");
                    }
                    else
                    {
                        saveErrLog("Table【" + dict["data_type"] + "】：执行sql出错,sync_id:" + dict["sync_id"] + "；" + sql + "; 返回0条记录。", DateTime.Now.ToString("yyyy-MM-dd") + "-sql_err");
                        returnDict["sync_id"] = dict["sync_id"];
                        returnDict["status"] = -1;
                        returnDict["remark"] = "这个sync_id处理失败了，请查看下sql_err.txt";
                    }
                }
                catch (Exception ex)
                {

                    saveErrLog(ex.Message + "\r\n" + ex.StackTrace.ToString(), "ex");
                    returnDict["sync_id"] = dict["sync_id"];
                    returnDict["status"] = -1;
                    returnDict["remark"] = "这个sync_id处理失败了，请查看下ex.txt";
                }
                finally
                {

                }
                return returnDict;
            }
            #endregion

            #region "声明变量"

            /// <summary>
            /// 写入INI文件
            /// </summary>
            /// <param name="section">节点名称[如[TypeName]]</param>
            /// <param name="key">键</param>
            /// <param name="val">值</param>
            /// <param name="filepath">文件路径</param>
            /// <returns></returns>
            [System.Runtime.InteropServices.DllImport("kernel32")]
            private static extern long WritePrivateProfileString(string section, string key, string val, string filepath);
            /// <summary>
            /// 读取INI文件
            /// </summary>
            /// <param name="section">节点名称</param>
            /// <param name="key">键</param>
            /// <param name="def">值</param>
            /// <param name="retval">stringbulider对象</param>
            /// <param name="size">字节大小</param>
            /// <param name="filePath">文件路径</param>
            /// <returns></returns>
            [DllImport("kernel32")]
            private static extern int GetPrivateProfileString(string section, string key, string def, StringBuilder retval, int size, string filePath);

            private static string strFilePath = Application.StartupPath + "\\lxerp.ini";//获取INI文件路径
            private static string strSec = ""; //INI文件名

            #endregion

            #region "自定义读取INI文件中的内容方法"
            /// <summary>
            /// 自定义读取INI文件中的内容方法
            /// </summary>
            /// <param name="Section">键</param>
            /// <param name="key">值</param>
            /// <returns></returns>
            private static string ContentValue(string Section, string key)
            {

                StringBuilder temp = new StringBuilder(1024);
                GetPrivateProfileString(Section, key, "", temp, 1024, strFilePath);
                return temp.ToString();
            }
            #endregion

            #region "获取sqlconn连接参数"

            private static string sqlconn;

            public static string Sqlconn
            {
                get { return sqlconn; }
                set { sqlconn = value; }
            }

            public static void getsqlconn()
            {
                if (File.Exists(strFilePath))//读取时先要判读INI文件是否存在
                {

                    //strSec = Path.GetFileNameWithoutExtension(strFilePath);
                    strSec = "Connection";
                    string Database = ContentValue(strSec, "Database");
                    string ServerName = ContentValue(strSec, "ServerName");
                    string LogId = ContentValue(strSec, "LogId");
                    string Logpass = ContentValue(strSec, "Logpass");
                    sqlconn = @"server=" + ServerName + ";database = " + Database + "; uid =" + LogId + "; pwd = " + Logpass + ";";
                }
            }
            #endregion

            private static string ip;

            public static string Ip
            {
                get { return DbHelper.ip; }
                set { DbHelper.ip = value; }
            }
            private static string port;

            public static string Port
            {
                get { return DbHelper.port; }
                set { DbHelper.port = value; }
            }
            private static string dir;

            public static string Dir
            {
                get { return DbHelper.dir; }
                set { DbHelper.dir = value; }
            }
            private static string second;

            public static string Second
            {
                get { return DbHelper.second; }
                set { DbHelper.second = value; }
            }

            private static string socketport;

            public static string Socketport
            {
                get { return DbHelper.socketport; }
                set { DbHelper.socketport = value; }
            }

            private static string protocol;

            public static string Protocol
            {
                get { return DbHelper.protocol; }
                set { DbHelper.protocol = value; }
            }
            private static string domain;

            public static string Domain
            {
                get { return DbHelper.domain; }
                set { DbHelper.domain = value; }
            }


            private static string fclub;

            public static string Fclub
            {
                get { return DbHelper.fclub; }
                set { DbHelper.fclub = value; }
            }

            private static string gymid;

            public static string Gymid
            {
                get { return DbHelper.gymid; }
                set { DbHelper.gymid = value; }
            }
            private static string ver;

            public static string Ver
            {
                get { return DbHelper.ver; }
                set { DbHelper.ver = value; }
            }

            //数据库是否存在延迟发送的两个字段
            private static bool hasDelayField;

            public static bool HasDelayField
            {
                get { return DbHelper.hasDelayField; }
                set { DbHelper.hasDelayField = value; }
            }
            //BG_JSON发送次数
            private static int sendCounts;

            public static int SendCounts
            {
                get { return sendCounts; }
                set { sendCounts = value; }
            }

            #region //根据传入的sql语句返回受影响的行数
            /// <summary>
            /// 根据传入的sql语句返回受影响的行数
            /// </summary>
            /// <param name="sql"></param>
            /// <returns></returns>
            public static int exec_sql(string sql)
            {
                int result = 0;
                SqlConnection conn = new SqlConnection(Sqlconn);
                try
                {

                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.CommandTimeout = 50000;
                    conn.Open();
                    result = cmd.ExecuteNonQuery();
                    if (DebugModel)
                        saveErrLog(sql, DateTime.Now.ToString("yyyy-MM-dd") + "-log");
                }
                catch (Exception ex)
                {
                    saveErrLog(sql + ex.Message, DateTime.Now.ToString("yyyy-MM-dd") + "-sql_err");
                    return result;
                }

                finally
                {
                    conn.Close();
                }

                return result;
            }
            #endregion

            #region //判断错误的sync_id是否在sync表存在
            /// <summary>
            /// 判断错误的sync_id是否在sync表存在
            /// </summary>
            /// <param name="sql"></param>
            /// <returns></returns>
            public static int ifSync_idExist(string sql)
            {
                int result = 0;
                SqlConnection conn = new SqlConnection(Sqlconn);
                try
                {


                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.CommandTimeout = 50000;
                    conn.Open();
                    result = int.Parse(cmd.ExecuteScalar().ToString());
                    //MessageBox.Show(result.ToString());
                }
                catch (Exception ex)
                {
                    saveErrLog(sql + ex.Message, DateTime.Now.ToString("yyyy-MM-dd") + "-sql_err");
                    return result;
                }

                finally
                {
                    conn.Close();

                }

                return result;
            }
            #endregion

            //加一把锁
            public static object locker = new object();

            #region //保存执行错误的sql
            public static void saveErrLog(string log, string txtName)
            {

                lock (locker)
                {
                    log = DateTime.Now.ToString() + " : " + log;
                    if (File.Exists(Application.StartupPath + "\\" + txtName + ".txt"))
                    {
                        StreamWriter SW;
                        SW = File.AppendText("" + txtName + ".txt");
                        SW.WriteLine(log + "\r\n");
                        SW.Close();

                    }
                    else
                    {
                        StreamWriter SW;
                        SW = File.CreateText("" + txtName + ".txt");
                        SW.WriteLine(log + "\r\n");
                        SW.Close();

                    }
                }
            }
            #endregion

            /// <summary>
            /// 把过滤规则赋值给dt
            /// </summary>
            public static void setupRulesDataTable()
            {
                //数据库连接
                SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=123.sqlite;Version=3;");
                m_dbConnection.Open();

                SQLiteCommand cmd = new SQLiteCommand(m_dbConnection);//实例化SQL命令
                cmd.CommandText = "select * from DataReplaceRules";
                //DataSet ds=SQLiteHelper.ExecuteDataset(cmd);
                DataSet ds = new DataSet();
                SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                da.Fill(ds);
                //给dt赋值
                dt = ds.Tables[0];

                da.Dispose();
                cmd.Connection.Close();
                cmd.Dispose();

            }

            //获取不需要加默认规则的字段值
            public static void getFilteringRules()
            {

                //实例化
                kindaccCodeArr = new ArrayList();
                paymentCodeArr = new ArrayList();

                if (!Directory.Exists(Application.StartupPath + "\\过滤规则"))
                {
                    //如果不存在过滤规则文件夹就用此默认规则

                    string[] kindaccCode = { "$54", "$56", "$74", "$76", "$77", "$80", "$82", "$85", "$86", "$88", "$90", "$99", "$91", "$93", "$95", "$97", "54", "56", "74", "76", "77", "80", "82", "85", "86", "88", "90", "91", "93", "95", "97", "99" };
                    string[] paymentCode = { "0001", "0002", "0003", "0004", "0006", "0007", "0008", "1001", "1003", "2001", "2002", "2003", "3001", "6999", "7999" };

                    for (int i = 0; i < kindaccCode.Length; i++)
                    {
                        kindaccCodeArr.Add(kindaccCode[i]);
                    }

                    for (int i = 0; i < paymentCode.Length; i++)
                    {
                        paymentCodeArr.Add(paymentCode[i]);
                    }
                    return;
                }

                //获取的路径
                string[] path = { Application.StartupPath + @"\过滤规则\sys_payment.txt", Application.StartupPath + @"\过滤规则\syskindacc.txt" };

                Thread worker = new Thread(delegate()
                {
                    for (int i = 0; i < path.Length; i++)
                    {
                        if (File.Exists(path[i]))
                        {
                            using (StreamReader sr = new StreamReader(path[i]))
                            {

                                string line = string.Empty;
                                while ((line = sr.ReadLine()) != null)
                                {
                                    if (line.Trim() != "")
                                    {
                                        if (path[i].Contains("sys_payment"))
                                            paymentCodeArr.Add(line.Trim());
                                        else
                                            kindaccCodeArr.Add(line.Trim());
                                    }
                                }
                            }

                        }
                    }
                }
                   );
                worker.IsBackground = true;
                worker.Start();

            }
            /// <summary>
            /// 查询对应的过滤替换字符串
            /// </summary>
            /// <param name="id"></param>
            /// <returns>字典</returns>
            public static Dictionary<string, string> plus_string(Dictionary<string, object> dict)
            {
                Dictionary<string, string> Dict_plus_string = new Dictionary<string, string>();
                DataRow[] drs = dt.Select("id='" + dict["LocalId"] + "'");
                if (drs.Length > 0)
                {
                    Dict_plus_string["string"] = drs[0][3].ToString();
                    Dict_plus_string["fclub"] = drs[0][2].ToString();
                }
                return Dict_plus_string;
            }
        }
    }
}
